{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2c444bc3-feaa-4d61-93d9-7ad46cb2648a",
   "metadata": {},
   "source": [
    "# SQlAlchemy使用\n",
    "* 使用Python语言编写\n",
    "* 一个SQL工具包\n",
    "* 一个QRM框架\n",
    "* 支持常见的数据库：Microsoft SQl Server，MySQL，Orancle，PostgreSQL，SQLite,Sybase"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9991da52-ad19-45aa-a8c9-f19b4e9b74c5",
   "metadata": {},
   "source": [
    "## 1 安装"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f165920a-ce4b-4d7e-9b40-40dbc56727a3",
   "metadata": {},
   "source": [
    "`pip install sqlalchemy mysqlclient -i https://mirrors.tuna.tsinghua.edu.cn/pypi/web/simple`\n",
    "\n",
    "* mysqlclient为sqlalchemy的依赖，必须安装"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "0a2adcac-b3e9-4b83-b126-5c453ee39a03",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f49ac4cd-82e3-4fd0-b876-88e65250eeb9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2.0.41'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看斑斑号\n",
    "sqlalchemy.__version__"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eaa063e3-66fd-495c-b9b1-6ce7cff8d861",
   "metadata": {},
   "source": [
    "## 2 连接数据库"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "650cf86d-e824-4738-9674-73063d333c3c",
   "metadata": {},
   "source": [
    "### 2.1 连接sqlite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "745144dd-c429-4499-bbe1-2c2623930892",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import sqlite3\n",
    "# connect = sqlite3.connect('test.db')\n",
    "# cursor = connect.cursor()\n",
    "# sql_query = 'create table students'\n",
    "# cursor.execute(sql_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "c72995b7-4895-4e6e-a74e-756db9b749f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# from sqlalchemy import create_engine\n",
    "# # 创建引擎\n",
    "# engine_sqlite = create_engine('sqlite:///test.db', echo=True)\n",
    "# connect_sqlite = engine_sqlite.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2bedee66-5ca8-48b6-abf1-efe19243b602",
   "metadata": {},
   "source": [
    "### 2.2 连接MySQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "57bb2a45-b809-4c20-8009-edd3c6c06ac1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "id": "c45e5904-89e6-4464-b671-e2beddb243e6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 14:19:31,287 INFO sqlalchemy.engine.Engine SELECT DATABASE()\n",
      "2025-07-13 14:19:31,288 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 14:19:31,289 INFO sqlalchemy.engine.Engine SELECT @@sql_mode\n",
      "2025-07-13 14:19:31,290 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 14:19:31,290 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names\n",
      "2025-07-13 14:19:31,291 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 14:19:31,293 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 14:19:31,293 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"student\")\n",
      "2025-07-13 14:19:31,293 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 14:19:31,294 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"student\")\n",
      "2025-07-13 14:19:31,295 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 14:19:31,295 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE student (\n",
      "\tid INTEGER NOT NULL, \n",
      "\tname VARCHAR(128), \n",
      "\tPRIMARY KEY (id)\n",
      ")\n",
      "\n",
      "\n",
      "2025-07-13 14:19:31,296 INFO sqlalchemy.engine.Engine [no key 0.00041s] ()\n",
      "2025-07-13 14:19:31,445 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "# 创建引擎\n",
    "engine_mysql = sqlalchemy.create_engine('mysql://root:qwer1234@localhost/mydatabase', echo=True)\n",
    "# 数据库取得连接\n",
    "connect = engine_mysql.connect()\n",
    "\n",
    "# 新建表\n",
    "meta_sqlite = sqlalchemy.MetaData()\n",
    "student = sqlalchemy.Table(\n",
    "    'student',\n",
    "    meta_sqlite,\n",
    "    sqlalchemy.Column('id', sqlalchemy.INTEGER, primary_key=True,autoincrement=True),\n",
    "    sqlalchemy.Column('name', sqlalchemy.String(128)),\n",
    ")\n",
    "\n",
    "meta_sqlite.create_all(engine_sqlite)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac344a03-667d-4068-9179-83968cceca48",
   "metadata": {},
   "source": [
    "#### 2.2.1 执行SQL原始语句"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "fc3d2b12-ac8c-4c54-bf3a-36ead584c37e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 原始查询语句\n",
    "query = sqlalchemy.text('select * from stuinfo_studentinfo')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "a6258826-bd00-4591-9d3c-5ad00155f5b8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 13:53:13,353 INFO sqlalchemy.engine.Engine select * from stuinfo_studentinfo\n",
      "2025-07-13 13:53:13,354 INFO sqlalchemy.engine.Engine [cached since 120.2s ago] ()\n"
     ]
    }
   ],
   "source": [
    "result_set = connect.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "e68afddd-f992-48b4-ba64-6cd8103b66ac",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, '2212010328', '王超冉', '男', '传媒学院', '5201', '网络新闻与传播（专科）', '2023级网络新闻与传播01班', 3, '2023', '20010920', '640302200109200511', '21642101951531', '2021', '在校', '有学籍', '在读', '202109', '普通大专', '汉族')\n"
     ]
    }
   ],
   "source": [
    "# 读取结果集\n",
    "for row in result_set:\n",
    "    print(row)\n",
    "    break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "c498910a-a1d2-4492-a5c8-67c35d9e530b",
   "metadata": {},
   "outputs": [],
   "source": [
    "connect.close()\n",
    "engine_mysql.dispose()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab55527a-0147-408e-ac8e-525bd7d3bd3b",
   "metadata": {},
   "source": [
    "## 3 数据库操作"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce1379ed-bad5-4713-9071-d0ae7096f18e",
   "metadata": {},
   "source": [
    "### 3.1 创建表\n",
    "* 使用MetaData，Table，Column以及字段类型字代码中来创建表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "id": "f71b970c-4ec4-4746-a69a-4a6efe81fdf9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 14:24:38,181 INFO sqlalchemy.engine.Engine SELECT DATABASE()\n",
      "2025-07-13 14:24:38,182 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 14:24:38,183 INFO sqlalchemy.engine.Engine SELECT @@sql_mode\n",
      "2025-07-13 14:24:38,183 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 14:24:38,184 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names\n",
      "2025-07-13 14:24:38,184 INFO sqlalchemy.engine.Engine [raw sql] ()\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, Date\n",
    "# 创建引擎\n",
    "engine_mysql = create_engine('mysql://root:qwer1234@localhost/sqlalchemy', echo=True)\n",
    "meta = MetaData() # 存储表结构的对象\n",
    "# 数据库取得连接\n",
    "connect = engine_mysql.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "id": "13e09b2f-c1d8-4a09-a320-cf01eecf5551",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 14:24:38,627 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 14:24:38,628 INFO sqlalchemy.engine.Engine DESCRIBE `sqlalchemy`.`student`\n",
      "2025-07-13 14:24:38,629 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 14:24:38,630 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE student (\n",
      "\tid INTEGER NOT NULL AUTO_INCREMENT, \n",
      "\tfirst_name VARCHAR(128) NOT NULL, \n",
      "\tlast_name VARCHAR(128), \n",
      "\tbirthday DATE NOT NULL, \n",
      "\tPRIMARY KEY (id), \n",
      "\tUNIQUE (first_name)\n",
      ")\n",
      "\n",
      "\n",
      "2025-07-13 14:24:38,631 INFO sqlalchemy.engine.Engine [no key 0.00044s] ()\n",
      "2025-07-13 14:24:39,187 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "student = Table(\n",
    "    'student',  # 表名\n",
    "    meta, # 表结构对象存储空间\n",
    "    Column('id', Integer, primary_key=True),\n",
    "    Column('first_name', String(128), unique=True, nullable=False),\n",
    "    Column('last_name', String(128)),\n",
    "    Column('birthday', Date, nullable=False),\n",
    ")\n",
    "\n",
    "meta.create_all(engine_mysql)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b9353c5-e397-42d5-bcb7-2e0da0b7832d",
   "metadata": {},
   "source": [
    "### 3.2 增加记录"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4537ac3-496d-4bdd-a2c4-e6dc3c4fba4e",
   "metadata": {},
   "source": [
    "* INSERT 查询"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "id": "8e233c86-3eff-40dd-99c7-8bd975ca1879",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INSERT INTO student (id, first_name, last_name, birthday) VALUES (:id, :first_name, :last_name, :birthday)\n",
      "2025-07-13 14:35:21,321 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 14:35:21,322 INFO sqlalchemy.engine.Engine INSERT INTO student (first_name, last_name, birthday) VALUES (%s, %s, %s)\n",
      "2025-07-13 14:35:21,322 INFO sqlalchemy.engine.Engine [cached since 511.6s ago] ('shi', 'jinfen', '1984-11-5')\n",
      "(3,)\n",
      "2025-07-13 14:35:21,348 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "# 插入一条记录\n",
    "# insert a record\n",
    "student_insert = student.insert()\n",
    "print(student_insert) # 实际上是生成一个查询语句模版\n",
    "insert_record = student_insert.values(first_name = 'shi', last_name = 'jinfen', birthday='1984-11-5')\n",
    "with engine_mysql.connect() as conn:\n",
    "    result = conn.execute(insert_record)\n",
    "    print(result.inserted_primary_key)\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "id": "83bb373e-b4a9-4e7b-8dbd-abbfdd0adb89",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 14:41:47,176 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 14:41:47,177 INFO sqlalchemy.engine.Engine INSERT INTO student (first_name, last_name, birthday) VALUES (%s, %s, %s)\n",
      "2025-07-13 14:41:47,177 INFO sqlalchemy.engine.Engine [generated in 0.00125s] [('赵静', '静子', '1984-8-18'), ('xuan', 'yusuan', '2006-5-18')]\n",
      "2025-07-13 14:41:47,178 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "# 插入多条记录\n",
    "# insert multiple record\n",
    "student_insert = student.insert()\n",
    "with engine_mysql.connect() as conn:\n",
    "    conn.execute(student_insert, [\n",
    "        {'first_name':'赵静','last_name':'静子','birthday':'1984-8-18'},\n",
    "        {'first_name':'xuan','last_name':'yusuan','birthday':'2006-5-18'},\n",
    "    ])\n",
    "    conn.commit()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a457e2a7-a49b-418b-9c70-3f14b27b3a0f",
   "metadata": {},
   "source": [
    "### 3.3 查询记录"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "760600cc-7195-454d-b178-ecd7c975c2dd",
   "metadata": {},
   "source": [
    "#### 3.3.1 查询所有记录\n",
    "\n",
    "  SQl查询：`select * from person`\n",
    "  \n",
    "  函数表达式：`person.select()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "id": "0027c56e-11b1-4a30-bf71-617ab213df26",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "# 创建引擎\n",
    "engine = sqlalchemy.create_engine('mysql://root:qwer1234@localhost/sqlalchemy', echo=True)\n",
    "\n",
    "meta = sqlalchemy.MetaData()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "id": "06f032e1-8f48-47db-ae72-d106ca658fc6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 14:50:06,505 INFO sqlalchemy.engine.Engine SELECT DATABASE()\n",
      "2025-07-13 14:50:06,506 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 14:50:06,507 INFO sqlalchemy.engine.Engine SELECT @@sql_mode\n",
      "2025-07-13 14:50:06,507 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 14:50:06,508 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names\n",
      "2025-07-13 14:50:06,509 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 14:50:06,510 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 14:50:06,510 INFO sqlalchemy.engine.Engine DESCRIBE `sqlalchemy`.`student`\n",
      "2025-07-13 14:50:06,511 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 14:50:06,512 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "student = Table(\n",
    "    'student',  # 表名\n",
    "    meta, # 表结构对象存储空间\n",
    "    Column('id', Integer, primary_key=True),\n",
    "    Column('first_name', String(128), unique=True, nullable=False),\n",
    "    Column('last_name', String(128)),\n",
    "    Column('birthday', Date, nullable=False),\n",
    ")\n",
    "\n",
    "meta.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "id": "2ad62c54-228d-4587-b7da-6f27de3cbb1d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 14:59:13,254 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 14:59:13,255 INFO sqlalchemy.engine.Engine SELECT student.id, student.first_name, student.last_name, student.birthday \n",
      "FROM student\n",
      "2025-07-13 14:59:13,255 INFO sqlalchemy.engine.Engine [cached since 490.2s ago] ()\n",
      "2025-07-13 14:59:13,256 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "with engine.connect() as conn:\n",
    "    query = student.select()\n",
    "    recordsets = conn.execute(query)\n",
    "    # results = recordsets.fetchall()\n",
    "    row_one = recordsets.fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60435f2b-b6f8-41b3-9cd1-f0077d16270d",
   "metadata": {},
   "source": [
    "* 循环提取结果集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "id": "2487e54a-a804-4dc6-8c9d-65836b0ad381",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3\n",
      "shi\n",
      "4\n",
      "赵静\n",
      "5\n",
      "xuan\n"
     ]
    }
   ],
   "source": [
    "for row in recordsets:\n",
    "    print(row[0])\n",
    "    print(row.first_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad04ca6d-a953-4314-adf4-c10c4a5134eb",
   "metadata": {},
   "source": [
    "* 使用提取函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "id": "b7951542-6f09-4de9-b15b-f1c0db03be3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "results = recordsets.fetchall()\n",
    "row_one = recordsets.fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8b30919-ca47-4733-ae0d-c9f58d362915",
   "metadata": {},
   "source": [
    "#### 3.3.2 条件查询\n",
    "* SQL查询：`select * from person where birthday > '2000-10-13' and id < 6`\n",
    "* 函数表达式：`person.select.where(person.c.birthday > '2000-10-13').where(person.c.id<6)`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 269,
   "id": "594104fe-5c07-4df3-b9d5-98a3dc159d7b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 15:05:57,425 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 15:05:57,426 INFO sqlalchemy.engine.Engine SELECT student.id, student.first_name, student.last_name, student.birthday \n",
      "FROM student \n",
      "WHERE student.birthday > %s\n",
      "2025-07-13 15:05:57,427 INFO sqlalchemy.engine.Engine [cached since 20.58s ago] ('2000-10-13',)\n",
      "\n",
      " [(1, 'Tome', 'Smith', datetime.date(2005, 5, 4)), (5, 'xuan', 'yusuan', datetime.date(2006, 5, 18))]\n",
      "2025-07-13 15:05:57,428 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "with engine.connect() as conn:\n",
    "    query = student.select().where(student.c.birthday > '2000-10-13')\n",
    "    recordsets = conn.execute(query)\n",
    "    results = recordsets.fetchall()\n",
    "    print('\\n',results)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bde4cca8-0809-468b-8dff-ab675c53182e",
   "metadata": {},
   "source": [
    "* 复杂条件查询\n",
    "\n",
    "  使用`from sqlalchemy.sql import and_, or_`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 273,
   "id": "ee3af666-0077-47b5-82ee-af0d6d80f59d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 15:10:36,594 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 15:10:36,595 INFO sqlalchemy.engine.Engine SELECT student.id, student.first_name, student.last_name, student.birthday \n",
      "FROM student \n",
      "WHERE student.first_name = %s OR student.birthday > %s AND student.id > %s\n",
      "2025-07-13 15:10:36,596 INFO sqlalchemy.engine.Engine [cached since 15.77s ago] ('shi', '2000-10-3', 1)\n",
      "\n",
      " [(3, 'shi', 'jinfen', datetime.date(1984, 11, 5)), (5, 'xuan', 'yusuan', datetime.date(2006, 5, 18))]\n",
      "2025-07-13 15:10:36,597 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy.sql import and_, or_\n",
    "with engine.connect() as conn:\n",
    "    query = student.select().where(\n",
    "        or_(\n",
    "            student.c.first_name == 'shi',\n",
    "            and_(\n",
    "                student.c.birthday > '2000-10-3',\n",
    "                student.c.id > 1\n",
    "            )\n",
    "        )\n",
    "    )\n",
    "    recordsets = conn.execute(query)\n",
    "    results = recordsets.fetchall()\n",
    "    print('\\n',results)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4998fbab-64c5-4389-9d85-cee252c6d578",
   "metadata": {},
   "source": [
    "### 3.4 更新记录\n",
    "* SQL语句：`update person set name='Maria' where id = 0`\n",
    "* 函数表达是：\n",
    "  * `person.update().where(person.c.id==0).values(name='Maria')`\n",
    "  * `updata(person).where(person.c.id==0).values(name='Maria')`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 287,
   "id": "88691f10-892d-4b6f-b388-6d99d4b76f9f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 15:20:32,400 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 15:20:32,401 INFO sqlalchemy.engine.Engine SELECT student.id, student.first_name, student.last_name, student.birthday \n",
      "FROM student\n",
      "2025-07-13 15:20:32,401 INFO sqlalchemy.engine.Engine [cached since 316.7s ago] ()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Maria', 'Smith', datetime.date(2005, 5, 4)),\n",
       " (3, 'shi', 'jinfen', datetime.date(1984, 11, 5)),\n",
       " (4, '赵静', '静子', datetime.date(1984, 8, 18)),\n",
       " (5, 'xuan', 'yusuan', datetime.date(2006, 5, 18))]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 15:20:32,403 INFO sqlalchemy.engine.Engine UPDATE student SET first_name=%s WHERE student.id = %s\n",
      "2025-07-13 15:20:32,404 INFO sqlalchemy.engine.Engine [cached since 85.37s ago] ('Maria', 1)\n",
      "2025-07-13 15:20:32,405 INFO sqlalchemy.engine.Engine COMMIT\n",
      "2025-07-13 15:20:32,406 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 15:20:32,406 INFO sqlalchemy.engine.Engine SELECT student.id, student.first_name, student.last_name, student.birthday \n",
      "FROM student\n",
      "2025-07-13 15:20:32,407 INFO sqlalchemy.engine.Engine [cached since 316.7s ago] ()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Maria', 'Smith', datetime.date(2005, 5, 4)),\n",
       " (3, 'shi', 'jinfen', datetime.date(1984, 11, 5)),\n",
       " (4, '赵静', '静子', datetime.date(1984, 8, 18)),\n",
       " (5, 'xuan', 'yusuan', datetime.date(2006, 5, 18))]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 15:20:32,410 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "with engine.connect() as conn:\n",
    "    query = student.select()\n",
    "    result = conn.execute(query)\n",
    "    display(result.fetchall())\n",
    "\n",
    "    # 更新数据\n",
    "    query = student.update().where(student.c.id == 1).values(first_name='Maria')\n",
    "    conn.execute(query)\n",
    "    conn.commit()\n",
    "\n",
    "    query = student.select()\n",
    "    result = conn.execute(query)\n",
    "    display(result.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "caa79bda-458b-4caf-a2d6-6e7bdcd4d1c3",
   "metadata": {},
   "source": [
    "### 3.5 删除记录\n",
    "* 所有记录\n",
    "    * SQL语句：`delete from person`\n",
    "    * 函数表达式：`person.delete()`\n",
    "* 删除符合条件的记录\n",
    "    * SQL语句：`delete from person where id > 6`\n",
    "    * 函数表达式：`person.delete().where(person.c.id > 6)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 289,
   "id": "fa0b4284-b5bc-42f8-861b-80208ed17d2f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 15:25:19,998 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 15:25:19,999 INFO sqlalchemy.engine.Engine SELECT student.id, student.first_name, student.last_name, student.birthday \n",
      "FROM student\n",
      "2025-07-13 15:25:19,999 INFO sqlalchemy.engine.Engine [cached since 604.3s ago] ()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Maria', 'Smith', datetime.date(2005, 5, 4)),\n",
       " (3, 'shi', 'jinfen', datetime.date(1984, 11, 5)),\n",
       " (4, '赵静', '静子', datetime.date(1984, 8, 18)),\n",
       " (5, 'xuan', 'yusuan', datetime.date(2006, 5, 18))]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 15:25:20,003 INFO sqlalchemy.engine.Engine DELETE FROM student WHERE student.id = %s\n",
      "2025-07-13 15:25:20,004 INFO sqlalchemy.engine.Engine [generated in 0.00110s] (1,)\n",
      "2025-07-13 15:25:20,024 INFO sqlalchemy.engine.Engine COMMIT\n",
      "2025-07-13 15:25:20,095 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 15:25:20,097 INFO sqlalchemy.engine.Engine SELECT student.id, student.first_name, student.last_name, student.birthday \n",
      "FROM student\n",
      "2025-07-13 15:25:20,098 INFO sqlalchemy.engine.Engine [cached since 604.4s ago] ()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(3, 'shi', 'jinfen', datetime.date(1984, 11, 5)),\n",
       " (4, '赵静', '静子', datetime.date(1984, 8, 18)),\n",
       " (5, 'xuan', 'yusuan', datetime.date(2006, 5, 18))]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 15:25:20,102 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "with engine.connect() as conn:\n",
    "    query = student.select()\n",
    "    result = conn.execute(query)\n",
    "    display(result.fetchall())\n",
    "\n",
    "    # 删除数据\n",
    "    query = student.delete().where(student.c.id == 1)\n",
    "    conn.execute(query)\n",
    "    conn.commit()\n",
    "\n",
    "    query = student.select()\n",
    "    result = conn.execute(query)\n",
    "    display(result.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d7f24ee-ddb3-4cf2-81a3-f66092338f40",
   "metadata": {},
   "source": [
    "## 4 关联表定义"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a6ad2b1-a9db-497a-8365-04d02b50e738",
   "metadata": {},
   "source": [
    "### 4.1 一对多的关联表\n",
    "* 设置外键\n",
    "* SQL语句：\n",
    "\n",
    "    CREATE TABLE employee (\n",
    "\n",
    "    \tid INTEGER NOT NULL AUTO_INCREMENT, \n",
    "    \n",
    "    \tdepartment_id INTEGER NOT NULL, \n",
    "    \n",
    "    \tname VARCHAR(128), \n",
    "    \n",
    "    \tbirthday DATE NOT NULL, \n",
    "    \n",
    "    \tPRIMARY KEY (id), \n",
    "    \n",
    "    \tFOREIGN KEY(department_id) REFERENCES department (id)\n",
    "  )\n",
    "\n",
    "* 函数表达式：\n",
    "  employee_table = sqlalchemy.Table(\n",
    "  \n",
    "    'employee', meta,\n",
    "  \n",
    "    sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),\n",
    "  \n",
    "    sqlalchemy.Column('department_id', sqlalchemy.Integer, sqlalchemy.ForeignKey('department.id'), nullable=False),\n",
    "  \n",
    "    sqlalchemy.Column('name', sqlalchemy.String(128)),\n",
    "  \n",
    "    sqlalchemy.Column('adress', sqlalchemy.String(128)),\n",
    "  \n",
    "    sqlalchemy.Column('birthday', sqlalchemy.Date,nullable=False),\n",
    "   \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "id": "70907c59-1513-4f5c-a7d6-8c0e6f9e6ec4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 15:50:05,092 INFO sqlalchemy.engine.Engine SELECT DATABASE()\n",
      "2025-07-13 15:50:05,093 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 15:50:05,094 INFO sqlalchemy.engine.Engine SELECT @@sql_mode\n",
      "2025-07-13 15:50:05,094 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 15:50:05,095 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names\n",
      "2025-07-13 15:50:05,095 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 15:50:05,096 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 15:50:05,097 INFO sqlalchemy.engine.Engine DESCRIBE `sqlalchemy`.`department`\n",
      "2025-07-13 15:50:05,097 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 15:50:05,099 INFO sqlalchemy.engine.Engine DESCRIBE `sqlalchemy`.`employee`\n",
      "2025-07-13 15:50:05,100 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 15:50:05,101 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE department (\n",
      "\tid INTEGER NOT NULL AUTO_INCREMENT, \n",
      "\tname VARCHAR(128) NOT NULL, \n",
      "\tPRIMARY KEY (id), \n",
      "\tUNIQUE (name)\n",
      ")\n",
      "\n",
      "\n",
      "2025-07-13 15:50:05,102 INFO sqlalchemy.engine.Engine [no key 0.00067s] ()\n",
      "2025-07-13 15:50:05,837 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE employee (\n",
      "\tid INTEGER NOT NULL AUTO_INCREMENT, \n",
      "\tdepartment_id INTEGER NOT NULL, \n",
      "\tname VARCHAR(128), \n",
      "\tbirthday DATE NOT NULL, \n",
      "\tPRIMARY KEY (id), \n",
      "\tFOREIGN KEY(department_id) REFERENCES department (id)\n",
      ")\n",
      "\n",
      "\n",
      "2025-07-13 15:50:05,838 INFO sqlalchemy.engine.Engine [no key 0.00052s] ()\n",
      "2025-07-13 15:50:06,396 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "engine = sqlalchemy.create_engine('mysql://root:qwer1234@localhost/sqlalchemy', echo=True)\n",
    "meta = sqlalchemy.MetaData()\n",
    "department_table = sqlalchemy.Table(\n",
    "    'department', meta,\n",
    "    sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),\n",
    "    sqlalchemy.Column('name', sqlalchemy.String(128), unique=True, nullable=False),\n",
    ")\n",
    "\n",
    "employee_table = sqlalchemy.Table(\n",
    "    'employee', meta,\n",
    "    sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),\n",
    "    sqlalchemy.Column('department_id', sqlalchemy.Integer, sqlalchemy.ForeignKey('department.id'), nullable=False),\n",
    "    sqlalchemy.Column('name', sqlalchemy.String(128)),\n",
    "    # sqlalchemy.Column('adress', sqlalchemy.String(128)),\n",
    "    sqlalchemy.Column('birthday', sqlalchemy.Date,nullable=False),\n",
    "   \n",
    ")\n",
    "\n",
    "meta.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 310,
   "id": "f5182255-24d5-45fc-ac73-f4ddb15f2750",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 15:50:08,896 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 15:50:08,897 INFO sqlalchemy.engine.Engine INSERT INTO department (name) VALUES (%s)\n",
      "2025-07-13 15:50:08,897 INFO sqlalchemy.engine.Engine [generated in 0.00137s] [('hr',), ('it',)]\n",
      "2025-07-13 15:50:08,901 INFO sqlalchemy.engine.Engine INSERT INTO employee (department_id, name, birthday) VALUES (%s, %s, %s)\n",
      "2025-07-13 15:50:08,901 INFO sqlalchemy.engine.Engine [generated in 0.00069s] [(1, '石金峰', '1984-11-05'), (2, '赵静', '1984-8-18')]\n",
      "2025-07-13 15:50:08,904 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "with engine.connect() as conn:\n",
    "    conn.execute(department_table.insert(), \n",
    "        [\n",
    "            {'name': 'hr'},\n",
    "            {'name': 'it'},\n",
    "        ]\n",
    "    )\n",
    "\n",
    "    conn.execute(employee_table.insert(),[\n",
    "        {'department_id':1,'name':'石金峰', 'birthday': '1984-11-05'},\n",
    "        {'department_id':2,'name':'赵静', 'birthday': '1984-8-18'},\n",
    "    ])\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56e435e1-0b83-4220-8cd0-5fd44278f9fa",
   "metadata": {},
   "source": [
    "### 4.2 一对一关联表查询"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5aecc722-9ce4-40f8-a00c-6b0af1947772",
   "metadata": {},
   "source": [
    "#### 4.2.1 查询示例1\n",
    "* 查询hr部门的所有员工及部门信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 318,
   "id": "2a686385-7217-4f00-872e-62b74da946db",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 16:03:30,062 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 16:03:30,062 INFO sqlalchemy.engine.Engine SELECT employee.id, employee.department_id, employee.name, employee.birthday, department.id AS id_1, department.name AS name_1 \n",
      "FROM employee INNER JOIN department ON employee.department_id = department.id \n",
      "WHERE department.name = %s\n",
      "2025-07-13 16:03:30,063 INFO sqlalchemy.engine.Engine [cached since 205.8s ago] ('hr',)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 1, '石金峰', datetime.date(1984, 11, 5), 1, 'hr'),\n",
       " (4, 1, '你好', datetime.date(1984, 11, 5), 1, 'hr'),\n",
       " (5, 1, '打架', datetime.date(1984, 11, 5), 1, 'hr')]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 16:03:30,065 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "with engine.connect() as conn:\n",
    "    # 表连接\n",
    "    join = employee_table.join(department_table, employee_table.c.department_id == department_table.c.id)\n",
    "    query = sqlalchemy.select(join).where(department_table.c.name == 'hr')\n",
    "    display(conn.execute(query).fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab5430e3-1214-4feb-b8a5-0e2907acebb3",
   "metadata": {},
   "source": [
    "#### 4.2.2 查询示例2\n",
    "* 查询hr部门的所有员工信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 325,
   "id": "7dfc05ee-a80c-43e6-9863-011c262265ab",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 16:10:33,794 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 16:10:33,795 INFO sqlalchemy.engine.Engine SELECT employee.id, employee.department_id, employee.name, employee.birthday \n",
      "FROM employee INNER JOIN department ON employee.department_id = department.id \n",
      "WHERE department.name = %s\n",
      "2025-07-13 16:10:33,795 INFO sqlalchemy.engine.Engine [cached since 209s ago] ('hr',)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 1, '石金峰', datetime.date(1984, 11, 5)),\n",
       " (4, 1, '你好', datetime.date(1984, 11, 5)),\n",
       " (5, 1, '打架', datetime.date(1984, 11, 5))]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 16:10:33,799 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "with engine.connect() as conn:\n",
    "    # 表连接\n",
    "    join = employee_table.join(department_table, employee_table.c.department_id == department_table.c.id)\n",
    "    # select参数为需要的字段\n",
    "    query = sqlalchemy.select(employee_table).select_from(join).where(department_table.c.name == 'hr')\n",
    "    display(conn.execute(query).fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18233caf-2a91-4a57-995c-7bcd1fa9844e",
   "metadata": {},
   "source": [
    "## 5 映射类\n",
    "* 基于定义映射类实现"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "195ec0ab-e7e2-4567-8fd0-e0e1e5c6110c",
   "metadata": {},
   "source": [
    "### 5.1 映射类基础\n",
    "* 使用`declarative_base`定义映射类的基类"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b14ed3db-2e63-45c4-8501-3676f5fbe80d",
   "metadata": {},
   "source": [
    "#### 5.1.1 定义映射类"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 348,
   "id": "eb1f4035-08e8-4286-a885-722940ab2efa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 引入基类\n",
    "from sqlalchemy import Integer, String, Date , Column\n",
    "from sqlalchemy.orm import declarative_base\n",
    "engine = sqlalchemy.create_engine('mysql://root:qwer1234@localhost/sqlalchemy', echo=True)\n",
    "Base = declarative_base()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 349,
   "id": "2c06a3b3-bf63-41f1-a35d-35916394b375",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 18:05:35,519 INFO sqlalchemy.engine.Engine SELECT DATABASE()\n",
      "2025-07-13 18:05:35,519 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 18:05:35,521 INFO sqlalchemy.engine.Engine SELECT @@sql_mode\n",
      "2025-07-13 18:05:35,521 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 18:05:35,522 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names\n",
      "2025-07-13 18:05:35,523 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 18:05:35,524 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 18:05:35,525 INFO sqlalchemy.engine.Engine DESCRIBE `sqlalchemy`.`person`\n",
      "2025-07-13 18:05:35,525 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 18:05:35,574 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "# 定义映射类\n",
    "class Person(Base):\n",
    "    __tablename__ = 'person' # 数据库表的对应表名称\n",
    "\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    name = Column(String(128), unique=True, nullable=False)\n",
    "    birthday = Column(Date, nullable=False)\n",
    "    address = Column(String(128), nullable=True)\n",
    "\n",
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c73a64a-1e9c-4f4f-9367-bd1c6542029e",
   "metadata": {},
   "source": [
    "#### 5.1.2 基于映射类添加记录\n",
    "* 使用session代替connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 350,
   "id": "6a2abd48-c6c2-4bb1-a1ce-62e90f701ae9",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.orm import sessionmaker\n",
    "Session = sessionmaker(bind=engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df8effe3-fdef-4f17-aa4d-2fb25c36f04c",
   "metadata": {},
   "source": [
    "* 添加单条记录"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 352,
   "id": "8c293ec2-a302-4ba3-bd5b-98de05a55482",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 18:06:50,858 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 18:06:50,860 INFO sqlalchemy.engine.Engine INSERT INTO person (name, birthday, address) VALUES (%s, %s, %s)\n",
      "2025-07-13 18:06:50,860 INFO sqlalchemy.engine.Engine [generated in 0.00086s] ('石金峰', '1984-11-05', '培华路1号')\n",
      "2025-07-13 18:06:50,871 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "session = Session()\n",
    "p = Person(name='石金峰', birthday='1984-11-05', address='培华路1号')\n",
    "session.add(p)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abf2268d-1dff-498a-905d-79b09923c7ec",
   "metadata": {},
   "source": [
    "* 添加多条记录"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 353,
   "id": "2278bd49-ac36-48c0-be2e-2e94334763fc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 19:46:08,011 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 19:46:08,013 INFO sqlalchemy.engine.Engine INSERT INTO person (name, birthday, address) VALUES (%s, %s, %s)\n",
      "2025-07-13 19:46:08,013 INFO sqlalchemy.engine.Engine [cached since 5960s ago] ('赵静', '1984-08-18', '山东菏泽')\n",
      "2025-07-13 19:46:08,077 INFO sqlalchemy.engine.Engine INSERT INTO person (name, birthday, address) VALUES (%s, %s, %s)\n",
      "2025-07-13 19:46:08,077 INFO sqlalchemy.engine.Engine [cached since 5960s ago] ('shiyuhan', '2009-5-4', '山东菏泽')\n",
      "2025-07-13 19:46:08,078 INFO sqlalchemy.engine.Engine INSERT INTO person (name, birthday, address) VALUES (%s, %s, %s)\n",
      "2025-07-13 19:46:08,079 INFO sqlalchemy.engine.Engine [cached since 5960s ago] ('shiyuxuan', '2016-5-18', '东北')\n",
      "2025-07-13 19:46:08,080 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "session = Session()\n",
    "ps = [\n",
    "    Person(name='赵静', birthday='1984-08-18', address='山东菏泽'),\n",
    "    Person(name='shiyuhan', birthday='2009-5-4', address='山东菏泽'),\n",
    "    Person(name='shiyuxuan', birthday='2016-5-18', address='东北'),\n",
    "]\n",
    "session.add_all(ps)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e152440d-5cab-4b4c-9077-40c285525c8a",
   "metadata": {},
   "source": [
    "#### 5.1.3 映射类查询\n",
    "* 查询所有记录"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 378,
   "id": "ac5a589d-ce1f-4d63-98c7-f9b9ce55c783",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 19:54:38,631 INFO sqlalchemy.engine.Engine SELECT person.id AS person_id, person.name AS person_name, person.birthday AS person_birthday, person.address AS person_address \n",
      "FROM person\n",
      "2025-07-13 19:54:38,632 INFO sqlalchemy.engine.Engine [cached since 275.8s ago] ()\n",
      "石金峰 1984-11-05\n",
      "赵静 1984-08-18\n",
      "shiyuhan 2009-05-04\n",
      "shiyuxuan 2016-05-18\n"
     ]
    }
   ],
   "source": [
    "result = session.query(Person).all()\n",
    "for person in result:\n",
    "    print(person.name,person.birthday)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5be7b005-ef76-4e6d-8ba4-04599756ff92",
   "metadata": {},
   "source": [
    "* 条件查询"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 401,
   "id": "c8dc0f2a-9adb-4af4-9c1a-2dabbf14b3fc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 20:04:10,877 INFO sqlalchemy.engine.Engine SELECT person.id AS person_id, person.name AS person_name, person.birthday AS person_birthday, person.address AS person_address \n",
      "FROM person \n",
      "WHERE person.birthday > %s AND person.id > %s\n",
      "2025-07-13 20:04:10,878 INFO sqlalchemy.engine.Engine [cached since 406.1s ago] ('2000-1-1', 2)\n",
      "3 shiyuhan 2009-05-04\n",
      "4 shiyuxuan 2016-05-18\n"
     ]
    }
   ],
   "source": [
    "result = session.query(Person).filter(Person.birthday > '2000-1-1').filter(Person.id > 2)\n",
    "for person in result:\n",
    "    print(person.id, person.name,person.birthday)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d77c632c-bd39-4093-8144-1ed6357d80f0",
   "metadata": {},
   "source": [
    "* 单条记录查询的返回\n",
    "  1. 使用first（）\n",
    "     \n",
    "     结果集如果是空的，调用first（）会异常报错"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 397,
   "id": "0a08e851-3898-44d6-b539-ddcb75f7bf39",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 20:01:51,062 INFO sqlalchemy.engine.Engine SELECT person.id AS person_id, person.name AS person_name, person.birthday AS person_birthday, person.address AS person_address \n",
      "FROM person \n",
      "WHERE person.id > %s \n",
      " LIMIT %s\n",
      "2025-07-13 20:01:51,063 INFO sqlalchemy.engine.Engine [cached since 39.88s ago] (1, 1)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(2, '赵静')"
      ]
     },
     "execution_count": 397,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = session.query(Person).filter(Person.id > 1).first()\n",
    "result.id, result.name"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b339f92b-ff5c-4de0-a637-746c81668077",
   "metadata": {},
   "source": [
    "      2.使用one()\n",
    "\n",
    "      结果集如果是多条或空的，只能是1条数据，调用one（）会异常报错"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6e13fcf-057d-482a-a2f4-441b8bfd2d56",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 399,
   "id": "76159675-0f81-405e-8020-9d00f65405b9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 20:02:22,661 INFO sqlalchemy.engine.Engine SELECT person.id AS person_id, person.name AS person_name, person.birthday AS person_birthday, person.address AS person_address \n",
      "FROM person \n",
      "WHERE person.id = %s\n",
      "2025-07-13 20:02:22,662 INFO sqlalchemy.engine.Engine [cached since 582.9s ago] (1,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(1, '石金峰')"
      ]
     },
     "execution_count": 399,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = session.query(Person).filter(Person.id == 1 ).one()\n",
    "result.id, result.name"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56fcdb2a-07a0-4e41-a995-7f6f7f968d6d",
   "metadata": {},
   "source": [
    "     3.使用scalar()\n",
    "\n",
    "      如果只有一条记录，或查不出来，和one（）相似"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 404,
   "id": "aed25ca3-b2ad-45fb-b221-24cbd9f03f7d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 20:05:02,440 INFO sqlalchemy.engine.Engine SELECT person.id AS person_id, person.name AS person_name, person.birthday AS person_birthday, person.address AS person_address \n",
      "FROM person \n",
      "WHERE person.id = %s\n",
      "2025-07-13 20:05:02,440 INFO sqlalchemy.engine.Engine [cached since 742.7s ago] (1,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(1, '石金峰')"
      ]
     },
     "execution_count": 404,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = session.query(Person).filter(Person.id == 1 ).scalar()\n",
    "result.id, result.name"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21f68a91-e47e-4457-807b-d33714ee0c6b",
   "metadata": {},
   "source": [
    "#### 5.1.4 映射类修改\n",
    "* 修改单条对象"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b6006c0-6aed-43d6-af49-7fa53eac59ab",
   "metadata": {},
   "source": [
    "1. 方法1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 413,
   "id": "fc584400-f114-4456-a754-96f65ed4ed6e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 20:11:33,987 INFO sqlalchemy.engine.Engine UPDATE person SET address=%s WHERE person.id = %s\n",
      "2025-07-13 20:11:33,988 INFO sqlalchemy.engine.Engine [cached since 92.29s ago] ('ccc', 1)\n",
      "2025-07-13 20:11:33,989 INFO sqlalchemy.engine.Engine SELECT person.id AS person_id, person.name AS person_name, person.birthday AS person_birthday, person.address AS person_address \n",
      "FROM person \n",
      "WHERE person.id = %s\n",
      "2025-07-13 20:11:33,990 INFO sqlalchemy.engine.Engine [cached since 1134s ago] (1,)\n",
      "2025-07-13 20:11:33,992 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "person = session.query(Person).filter(Person.id==1).one()\n",
    "person.address = 'ccc'\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "398f11d6-e25b-49d3-890a-e3cfe690ef25",
   "metadata": {},
   "source": [
    "2. 方法2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 414,
   "id": "d437db80-1adc-4026-96fa-164fc343a5b6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 20:11:54,653 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 20:11:54,654 INFO sqlalchemy.engine.Engine UPDATE person SET address=%s WHERE person.id = %s\n",
      "2025-07-13 20:11:54,654 INFO sqlalchemy.engine.Engine [cached since 112.9s ago] ('培华', 1)\n",
      "2025-07-13 20:11:54,656 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "session.query(Person).filter(Person.id == 1).update({Person.address: '培华'})\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 416,
   "id": "f7f6e030-d415-46ad-a69e-c5258178d754",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 20:15:12,364 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 20:15:12,365 INFO sqlalchemy.engine.Engine UPDATE person SET address=%s WHERE person.id > %s\n",
      "2025-07-13 20:15:12,366 INFO sqlalchemy.engine.Engine [generated in 0.00080s] ('BeiJin', 2)\n",
      "2025-07-13 20:15:12,374 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "# 批量修改\n",
    "session.query(Person).filter(Person.id > 2 ).update({Person.address: 'BeiJin'})\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "432e291a-c6a2-453b-961f-86d2be7c0b56",
   "metadata": {},
   "source": [
    "### 5.2 新的映射方式\n",
    "* SQL2.0之后提供的更加方便的写法"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fab62040-b5d0-424b-bae5-b2626aecd5e4",
   "metadata": {},
   "source": [
    "#### 5.2.1 Mapped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 423,
   "id": "934f892b-2dfa-4fb8-8b43-864d5cfe9a1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用Mapped来映射字段\n",
    "import datetime\n",
    "from sqlalchemy.orm import Mapped, mapped_column, declarative_base\n",
    "from sqlalchemy import String,create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 433,
   "id": "988c771e-7aa8-4207-9089-3f886332b56a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 20:31:19,475 INFO sqlalchemy.engine.Engine SELECT DATABASE()\n",
      "2025-07-13 20:31:19,476 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 20:31:19,477 INFO sqlalchemy.engine.Engine SELECT @@sql_mode\n",
      "2025-07-13 20:31:19,477 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 20:31:19,478 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names\n",
      "2025-07-13 20:31:19,478 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 20:31:19,479 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 20:31:19,480 INFO sqlalchemy.engine.Engine DESCRIBE `sqlalchemy`.`customer`\n",
      "2025-07-13 20:31:19,480 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 20:31:19,803 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "engine = create_engine('mysql://root:qwer1234@localhost/sqlalchemy', echo=True)\n",
    "Base = declarative_base()\n",
    "\n",
    "class Customer(Base):\n",
    "    __tablename__ = 'customer'\n",
    "\n",
    "    id: Mapped[int] = mapped_column(primary_key=True)\n",
    "    name: Mapped[str] = mapped_column(String(128), unique=True, nullable=False)\n",
    "    birthday: Mapped[datetime.datetime]\n",
    "\n",
    "Base.metadata.create_all(engine)\n",
    "Session = sessionmaker(bind=engine)\n",
    "session = Session()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 434,
   "id": "c34858a9-6075-4d7e-9a5a-8836af290ab4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 20:31:20,141 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 20:31:20,142 INFO sqlalchemy.engine.Engine INSERT INTO customer (name, birthday) VALUES (%s, %s)\n",
      "2025-07-13 20:31:20,143 INFO sqlalchemy.engine.Engine [generated in 0.00080s] ('shijinfeng', '1984-11-05')\n",
      "2025-07-13 20:31:20,152 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "c = Customer(name = 'shijinfeng', birthday = '1984-11-05')\n",
    "session.add(c)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f674917-83f5-4d7c-b26a-0d0f8ae9e7f5",
   "metadata": {},
   "source": [
    "#### 5.2.1 与定义字段定义信息\n",
    "* 使用Annotated"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 442,
   "id": "d102defe-2264-4a5a-ba1a-ffc13d1563d3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 20:50:25,694 INFO sqlalchemy.engine.Engine SELECT DATABASE()\n",
      "2025-07-13 20:50:25,695 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 20:50:25,697 INFO sqlalchemy.engine.Engine SELECT @@sql_mode\n",
      "2025-07-13 20:50:25,698 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 20:50:25,699 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names\n",
      "2025-07-13 20:50:25,700 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 20:50:25,700 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 20:50:25,701 INFO sqlalchemy.engine.Engine DESCRIBE `sqlalchemy`.`customer`\n",
      "2025-07-13 20:50:25,701 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 20:50:25,770 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE customer (\n",
      "\tid INTEGER NOT NULL AUTO_INCREMENT, \n",
      "\tname VARCHAR(128) NOT NULL, \n",
      "\tbirthday DATETIME NOT NULL, \n",
      "\tcity VARCHAR(128), \n",
      "\tcreate_time DATETIME NOT NULL DEFAULT (now()), \n",
      "\tPRIMARY KEY (id), \n",
      "\tUNIQUE (name)\n",
      ")\n",
      "\n",
      "\n",
      "2025-07-13 20:50:25,770 INFO sqlalchemy.engine.Engine [no key 0.00054s] ()\n",
      "2025-07-13 20:50:26,596 INFO sqlalchemy.engine.Engine COMMIT\n",
      "2025-07-13 20:50:26,599 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 20:50:26,600 INFO sqlalchemy.engine.Engine INSERT INTO customer (name, birthday, city) VALUES (%s, %s, %s)\n",
      "2025-07-13 20:50:26,600 INFO sqlalchemy.engine.Engine [generated in 0.00044s] ('shijinfeng', '1984-11-05', None)\n",
      "2025-07-13 20:50:26,602 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "from typing_extensions import Annotated\n",
    "from sqlalchemy.sql import func\n",
    "\n",
    "\n",
    "# 定义注解\n",
    "int_pk = Annotated[int, mapped_column(primary_key=True)]\n",
    "required_unique_name = Annotated[str, mapped_column(String(128), unique=True, nullable=False)]\n",
    "timestrap_default_now = Annotated[datetime.datetime, mapped_column(nullable=False, server_default=func.now())]\n",
    "\n",
    "engine = create_engine('mysql://root:qwer1234@localhost/sqlalchemy', echo=True)\n",
    "Base = declarative_base()\n",
    "\n",
    "class Customer(Base):\n",
    "    __tablename__ = 'customer'\n",
    "\n",
    "    id: Mapped[int_pk]\n",
    "    name: Mapped[required_unique_name]\n",
    "    birthday: Mapped[datetime.datetime]\n",
    "    city: Mapped[str] = mapped_column(String(128),nullable=True)\n",
    "    create_time:Mapped[timestrap_default_now]\n",
    "\n",
    "Base.metadata.create_all(engine)\n",
    "Session = sessionmaker(bind=engine)\n",
    "session = Session()\n",
    "\n",
    "c = Customer(name='shijinfeng', birthday='1984-11-05')\n",
    "session.add(c)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7fa3c6a3-2170-4352-a4a7-0cadce6705d5",
   "metadata": {},
   "source": [
    "## 6 一对多ORM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 505,
   "id": "ec2d35d7-356d-4985-a38f-c3c6640d2e72",
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "\n",
    "from typing_extensions import Annotated\n",
    "from sqlalchemy.sql import func\n",
    "from sqlalchemy.orm import Mapped, mapped_column, declarative_base, sessionmaker, relationship\n",
    "from sqlalchemy import String,create_engine,ForeignKey\n",
    "from typing import List\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 515,
   "id": "79989681-e8d8-4229-a4fd-8c096e7a6f0f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 21:55:11,958 INFO sqlalchemy.engine.Engine SELECT DATABASE()\n",
      "2025-07-13 21:55:11,959 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 21:55:11,960 INFO sqlalchemy.engine.Engine SELECT @@sql_mode\n",
      "2025-07-13 21:55:11,961 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 21:55:11,962 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names\n",
      "2025-07-13 21:55:11,963 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 21:55:11,964 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 21:55:11,964 INFO sqlalchemy.engine.Engine DESCRIBE `sqlalchemy`.`department`\n",
      "2025-07-13 21:55:11,965 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 21:55:11,967 INFO sqlalchemy.engine.Engine DESCRIBE `sqlalchemy`.`employee`\n",
      "2025-07-13 21:55:11,967 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2025-07-13 21:55:11,968 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "engine = create_engine('mysql://root:qwer1234@localhost/sqlalchemy', echo=True)\n",
    "Base = declarative_base()\n",
    "\n",
    "# 定义注解\n",
    "int_pk = Annotated[int, mapped_column(primary_key=True)]\n",
    "required_unique_name = Annotated[str, mapped_column(String(128), unique=True, nullable=False)]\n",
    "timestrap_default_now = Annotated[datetime.datetime, mapped_column(nullable=False, server_default=func.now())]\n",
    "\n",
    "engine = create_engine('mysql://root:qwer1234@localhost/sqlalchemy', echo=True)\n",
    "Base = declarative_base()\n",
    "Session = sessionmaker(bind=engine)\n",
    "\n",
    "class Department(Base):\n",
    "    __tablename__ = 'department'\n",
    "\n",
    "    id: Mapped[int_pk]\n",
    "    name: Mapped[required_unique_name]\n",
    "\n",
    "    # 定义映射关系\n",
    "    # 一般情况下一对多的情况下，多的一方不建议定义映射关系\n",
    "    # 使用back_populates进行双向关联\n",
    "    employees: Mapped[List[\"Employee\"]] = relationship(back_populates='department')\n",
    "    # create_time: Mapped[timestrap_default_now]\n",
    "\n",
    "    def __repr__(self):\n",
    "        return f'id:{self.id}, name:{self.name}'\n",
    "\n",
    "class Employee(Base):\n",
    "    __tablename__ = 'employee'\n",
    "    \n",
    "    id: Mapped[int_pk]\n",
    "    dep_id: Mapped[int] = mapped_column(ForeignKey('department.id'))\n",
    "    name: Mapped[required_unique_name]\n",
    "    birthday: Mapped[datetime.datetime]\n",
    "\n",
    "    # 使用backref双向关联\n",
    "    # department: Mapped[Department] = relationship(backref='employees') # 定义映射关系，懒惰模式查询 ,backref定义反向查询\n",
    "    # department: Mapped[Department] = relationship(lazy=False, backref='employees') # 非懒惰模式查询，backref定义反向查询\n",
    "\n",
    "    # 定义映射关系\n",
    "    department: Mapped[Department] = relationship(lazy=False, back_populates='employees')\n",
    "    \n",
    "\n",
    "    def __repr__(self):\n",
    "        return f'id:{self.id}, dep_id:{self.dep_id}, name:{self.name}, birthday:{self.birthday}'\n",
    "\n",
    "Base.metadata.create_all(engine)\n",
    "session = Session()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 474,
   "id": "45d1064e-c25d-4754-b779-b9da5e9c236e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 21:31:55,313 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 21:31:55,315 INFO sqlalchemy.engine.Engine INSERT INTO department (name) VALUES (%s)\n",
      "2025-07-13 21:31:55,316 INFO sqlalchemy.engine.Engine [generated in 0.00115s] ('hr',)\n",
      "2025-07-13 21:31:55,320 INFO sqlalchemy.engine.Engine INSERT INTO department (name) VALUES (%s)\n",
      "2025-07-13 21:31:55,320 INFO sqlalchemy.engine.Engine [cached since 0.005195s ago] ('it',)\n",
      "2025-07-13 21:31:55,323 INFO sqlalchemy.engine.Engine INSERT INTO employee (dep_id, name, birthday) VALUES (%s, %s, %s)\n",
      "2025-07-13 21:31:55,324 INFO sqlalchemy.engine.Engine [generated in 0.00113s] (1, 'shijinfeng', '1984-11-05')\n",
      "2025-07-13 21:31:55,380 INFO sqlalchemy.engine.Engine INSERT INTO employee (dep_id, name, birthday) VALUES (%s, %s, %s)\n",
      "2025-07-13 21:31:55,381 INFO sqlalchemy.engine.Engine [cached since 0.05748s ago] (1, 'benben', '1984-11-05')\n",
      "2025-07-13 21:31:55,382 INFO sqlalchemy.engine.Engine INSERT INTO employee (dep_id, name, birthday) VALUES (%s, %s, %s)\n",
      "2025-07-13 21:31:55,383 INFO sqlalchemy.engine.Engine [cached since 0.05963s ago] (1, 'jiajia', '1984-11-05')\n",
      "2025-07-13 21:31:55,385 INFO sqlalchemy.engine.Engine INSERT INTO employee (dep_id, name, birthday) VALUES (%s, %s, %s)\n",
      "2025-07-13 21:31:55,385 INFO sqlalchemy.engine.Engine [cached since 0.06197s ago] (2, 'keke', '1984-11-05')\n",
      "2025-07-13 21:31:55,387 INFO sqlalchemy.engine.Engine INSERT INTO employee (dep_id, name, birthday) VALUES (%s, %s, %s)\n",
      "2025-07-13 21:31:55,388 INFO sqlalchemy.engine.Engine [cached since 0.06434s ago] (2, 'zhuzhu', '1984-11-05')\n",
      "2025-07-13 21:31:55,388 INFO sqlalchemy.engine.Engine INSERT INTO employee (dep_id, name, birthday) VALUES (%s, %s, %s)\n",
      "2025-07-13 21:31:55,389 INFO sqlalchemy.engine.Engine [cached since 0.06578s ago] (2, 'dengdeng', '1984-11-05')\n",
      "2025-07-13 21:31:55,390 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "def insert_records(session):\n",
    "    d1 = Department(name='hr')\n",
    "    d2 = Department(name='it')\n",
    "    # session.add(d1)\n",
    "    # session.flush() # 强行加载缓存,不方便且效率不高\n",
    "    es = [\n",
    "        Employee(name='shijinfeng',birthday='1984-11-05',department=d1),\n",
    "        Employee(name='benben',birthday='1984-11-05',department=d1),\n",
    "        Employee(name='jiajia',birthday='1984-11-05',department=d1),\n",
    "        Employee(name='keke',birthday='1984-11-05',department=d2),\n",
    "        Employee(name='zhuzhu',birthday='1984-11-05',department=d2),\n",
    "        Employee(name='dengdeng',birthday='1984-11-05',department=d2),\n",
    "        \n",
    "         ]\n",
    "    session.add_all(es)\n",
    "    session.commit()\n",
    "insert_records(session)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86def61f-5af0-465e-ab1c-9fa7bcd35009",
   "metadata": {},
   "source": [
    "* 查询"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 516,
   "id": "795a0434-edd4-41e7-9715-b97d7171fc39",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 21:55:19,824 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2025-07-13 21:55:19,826 INFO sqlalchemy.engine.Engine SELECT employee.id AS employee_id, employee.dep_id AS employee_dep_id, employee.name AS employee_name, employee.birthday AS employee_birthday, department_1.id AS department_1_id, department_1.name AS department_1_name \n",
      "FROM employee LEFT OUTER JOIN department AS department_1 ON department_1.id = employee.dep_id \n",
      "WHERE employee.id = %s\n",
      "2025-07-13 21:55:19,827 INFO sqlalchemy.engine.Engine [generated in 0.00103s] (1,)\n",
      "\n",
      " id:1, dep_id:1, name:shijinfeng, birthday:1984-11-05 00:00:00\n",
      "\n",
      " id:1, name:hr\n"
     ]
    }
   ],
   "source": [
    "def select_employee(session: session):\n",
    "    emp = session.query(Employee).filter(Employee.id == 1).one()\n",
    "    print('\\n',emp)\n",
    "    # 关联数据查询\n",
    "    print('\\n',emp.department)\n",
    "\n",
    "select_employee(session)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 517,
   "id": "1d4589e2-42d0-4be7-880f-03ec2ff8143a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-07-13 21:55:22,638 INFO sqlalchemy.engine.Engine SELECT department.id AS department_id, department.name AS department_name \n",
      "FROM department \n",
      "WHERE department.name = %s\n",
      "2025-07-13 21:55:22,638 INFO sqlalchemy.engine.Engine [generated in 0.00059s] ('it',)\n",
      "2025-07-13 21:55:22,640 INFO sqlalchemy.engine.Engine SELECT employee.id AS employee_id, employee.dep_id AS employee_dep_id, employee.name AS employee_name, employee.birthday AS employee_birthday \n",
      "FROM employee \n",
      "WHERE %s = employee.dep_id\n",
      "2025-07-13 21:55:22,641 INFO sqlalchemy.engine.Engine [generated in 0.00078s] (2,)\n",
      "[id:4, dep_id:2, name:keke, birthday:1984-11-05 00:00:00, id:5, dep_id:2, name:zhuzhu, birthday:1984-11-05 00:00:00, id:6, dep_id:2, name:dengdeng, birthday:1984-11-05 00:00:00]\n"
     ]
    }
   ],
   "source": [
    "def select_department(session: session):\n",
    "    dep = session.query(Department).filter(Department.name=='it').one()\n",
    "    # print(dep)\n",
    "    print(dep.employees)\n",
    "\n",
    "select_department(session)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78e40606-0ad3-41de-8391-18cb44ac631b",
   "metadata": {},
   "source": [
    "## 7 多对多ORM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f5389be-fe36-48c1-b373-17bfc6fd4cd9",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
